package org.gatorojo.dao;

import java.util.List;
import java.util.ArrayList;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.gatorojo.exception.GatoRojoException;
import org.gatorojo.model.Product;
import org.gatorojo.dao.connection.ConnectionManager;

public class ProductService implements IProductService {
	private Connection connection;
	
	public Product readProduct(int code) {
		connection = ConnectionManager.openConnection();
		Product product = null;
	    		
	    String query = "select * from product where code="+code;
	    try {
	    	Statement statement = connection.createStatement();
	    	ResultSet result = statement.executeQuery(query);
	    	while (result.next()) {
	    		String name = result.getString("name");
	    		double stock = result.getDouble("stock");
	    		product = new Product(code, name, stock);		    	
	    	}	    		    
	    } catch (SQLException ex) {
	    	System.out.println(ex.getMessage());
	    } finally {
	    	ConnectionManager.closeConnection(connection);
	    }
	    return product;
	}
	
	public void addProduct(Product product) {
		connection = ConnectionManager.openConnection();		
	    		
		String query = "insert into product(code,name,stock) values(?,?,?)";	    
	    
	    try {
	    	PreparedStatement preparedStatement = connection.prepareStatement(query);    
		    preparedStatement.setInt(1, product.getCode());
		    preparedStatement.setString(2, product.getName());
		    preparedStatement.setDouble(3, product.getStock());
		    
	    	preparedStatement.executeUpdate();	    		    		   
	    } catch (SQLException ex) {
	    	System.out.println(ex.getMessage());
	    } finally {
	    	ConnectionManager.closeConnection(connection);
	    }
	}	
	
	public void updateProduct(Product product) {
		connection = ConnectionManager.openConnection();
		
		String query = "update product set name=?, stock=? where code=?";		
	    
	    try {
	    	PreparedStatement preparedStatement = connection.prepareStatement(query);    		    
		    preparedStatement.setString(1, product.getName());
		    preparedStatement.setDouble(2, product.getStock());
		    preparedStatement.setDouble(3, product.getCode());
		    
	    	preparedStatement.executeUpdate();
	    } catch (SQLException ex) {
	    	System.out.println(ex.getMessage());
	    } finally {
	    	ConnectionManager.closeConnection(connection);
	    }	
	}
	
	public void deleteProduct(int code) {
		connection = ConnectionManager.openConnection();
		
		String query = "delete from product where code=?";		
	    
	    try {
	    	PreparedStatement preparedStatement = connection.prepareStatement(query);    
		    preparedStatement.setInt(1, code);		    
		    
	    	preparedStatement.executeUpdate();	    		    		   
	    } catch (SQLException ex) {
	    	System.out.println(ex.getMessage());
	    } finally {
	    	ConnectionManager.closeConnection(connection);
	    }
	}
	
	public List<Product> listProduct() throws GatoRojoException {
		connection = ConnectionManager.openConnection();
		List<Product> products = new ArrayList<Product>();
		Product product = null;
	    		
	    String query = "select * from product";
	    try {
	    	Statement statement = connection.createStatement();
	    	ResultSet result = statement.executeQuery(query);
	    	while (result.next()) {
	    		int code = result.getInt("code");
	    		String name = result.getString("name");
	    		double stock = result.getDouble("stock");
	    		product = new Product(code, name, stock);
		    	products.add(product);
	    	}
	    } catch (SQLException ex) {
	    	System.out.println(ex.getMessage());
	    } finally {
	    	ConnectionManager.closeConnection(connection);
	    }
	    return products;
	}
}